package com.echo.dao;

import com.echo.dao.UserDao;
import com.echo.entity.Article;
import com.echo.entity.User;
import com.echo.util.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 文章数据访问层：负责articles表的CRUD
 */
public class ArticleDao {
    // 依赖用户DAO，用于根据用户名查询用户ID
    private final UserDao userDao = new UserDao();

    /**
     * 新增文章（发布文章时调用）
     * @param article 文章对象
     * @return 生成的文章ID
     * @throws SQLException 数据库操作异常
     */
    public Integer insert(Article article) throws SQLException {
        // 测试模式特殊处理：如果用户名以test_开头，则跳过用户检查
        String username = article.getAuthorUsername();
//        boolean isTestMode = username != null && username.startsWith("test_");
        User user = null;
        

            // 正常模式：根据用户名查询用户信息
        try {
            user = userDao.findByUsername(username);
        }catch(SQLException e){
            throw new SQLException("查询用户失败：" + username, e);
        }

            if (user == null) {
                throw new SQLException("未找到用户：" + username);
            }
        
        // SQL语句包含所有必需的字段
        String sql = "INSERT INTO articles (title, content, html_content, author_username, view_count, status, create_time, update_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
            // 填充参数（防SQL注入）
            pstmt.setString(1, article.getTitle());
            pstmt.setString(2, article.getContent());
            // 设置html_content，如果为null则设置为空字符串
            pstmt.setString(3, article.getHtmlContent() != null ? article.getHtmlContent() : "");
            // 设置author_username（用户名）
            pstmt.setString(4, article.getAuthorUsername());
            // 设置浏览量
            pstmt.setInt(5, article.getViewCount() != null ? article.getViewCount() : 0);
            // 设置状态
            pstmt.setInt(6, article.getStatus() != null ? article.getStatus() : 1);
            // 设置时间
            pstmt.setTimestamp(7, new java.sql.Timestamp(article.getCreateTime().getTime()));
            pstmt.setTimestamp(8, new java.sql.Timestamp(article.getUpdateTime().getTime()));
            
            pstmt.executeUpdate(); // 执行插入
            
            // 获取生成的ID
            try (ResultSet rs = pstmt.getGeneratedKeys()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
            return -1; // 未获取到ID
        }
    }

    /**
     * 查询所有文章（按创建时间倒序），关联作者昵称
     * @return 文章列表（含作者昵称）
     * @throws SQLException 数据库操作异常
     */
    public List<Article> findAllOrderByCreateTimeDesc() throws SQLException {
        List<Article> articleList = new ArrayList<>();
        // 关联查询：从articles表和users表获取文章信息+作者用户名
        String sql = "SELECT a.id, a.title, a.content, a.html_content, a.author_username, a.status, " +
                  "a.create_time, a.update_time " +
                  "FROM articles a " +
                "LEFT JOIN users u ON a.author_username = u.username " +
                "WHERE a.status = 1 " +
                "ORDER BY a.create_time DESC";
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            // 结果集映射为Article对象
            while (rs.next()) {
                Article article = new Article();
                article.setId(rs.getInt("id"));
        article.setTitle(rs.getString("title"));
        article.setContent(rs.getString("content"));
        article.setHtmlContent(rs.getString("html_content"));
        article.setAuthorUsername(rs.getString("author_username"));
        article.setStatus(rs.getInt("status"));
        article.setCreateTime(rs.getTimestamp("create_time"));
        article.setUpdateTime(rs.getTimestamp("update_time"));
                articleList.add(article);
            }
        }
        return articleList;
    }

    /**
     * 根据文章ID查询文章详情（关联作者昵称）
     * @param id 文章ID
     * @return 文章对象（含作者昵称），无结果返回null
     * @throws SQLException 数据库操作异常
     */
    public Article findById(Integer id) throws SQLException {
        if (id == null || id <= 0) {
            return null;
        }
        String sql = "SELECT a.id, a.title, a.content, a.html_content, a.author_username, " +
                "a.view_count, a.status, a.create_time, a.update_time " +
                "FROM articles a " +
                "LEFT JOIN users u ON a.author_username = u.username " +
                "WHERE a.id = ?";
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Article article = new Article();
                    article.setId(rs.getInt("id"));
                    article.setTitle(rs.getString("title"));
                    article.setContent(rs.getString("content"));
                    article.setHtmlContent(rs.getString("html_content"));
                    article.setAuthorUsername(rs.getString("author_username"));
                    article.setViewCount(rs.getInt("view_count"));
                    article.setStatus(rs.getInt("status"));
                    article.setCreateTime(rs.getTimestamp("create_time"));
                    article.setUpdateTime(rs.getTimestamp("update_time"));
                    return article;
                }
            }
        }
        return null;
    }
    
    /**
     * 根据作者用户名查询文章列表（按创建时间倒序）
     * @param authorUsername 作者用户名
     * @return 文章列表
     * @throws SQLException 数据库操作异常
     */
    public List<Article> findByAuthorUsername(String authorUsername) throws SQLException {
        List<Article> articleList = new ArrayList<>();
        if (authorUsername == null || authorUsername.isEmpty()) {
            return articleList;
        }
        
        String sql = "SELECT a.id, a.title, a.content, a.html_content, a.author_username, " +
                "a.view_count, a.create_time, a.update_time " +
                "FROM articles a " +
                "LEFT JOIN users u ON a.author_username = u.username " +
                "WHERE a.author_username = ? " +
                "ORDER BY a.create_time DESC";
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, authorUsername);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Article article = new Article();
                    article.setId(rs.getInt("id"));
        article.setTitle(rs.getString("title"));
        article.setContent(rs.getString("content"));
        article.setHtmlContent(rs.getString("html_content"));
        article.setAuthorUsername(rs.getString("author_username"));
        article.setViewCount(rs.getInt("view_count"));
        article.setCreateTime(rs.getTimestamp("create_time"));
        article.setUpdateTime(rs.getTimestamp("update_time"));
                    articleList.add(article);
                }
            }
        }
        return articleList;
    }
    
    /**
     * 更新文章信息
     * @param article 文章对象（需包含id）
     * @return 影响行数（1=成功，0=失败）
     * @throws SQLException 数据库操作异常
     */
    public int update(Article article) throws SQLException {
        System.out.println("ArticleDao: 开始更新数据库，文章ID: " + article.getId());
        
        if (article == null || article.getId() == null) {
            System.out.println("ArticleDao: 文章对象或ID为null，更新失败");
            return 0;
        }
        
        String sql = "UPDATE articles SET title = ?, content = ?, html_content = ?, update_time = ? WHERE id = ?";
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            System.out.println("ArticleDao: 获取数据库连接成功，准备执行SQL更新");
            // 填充参数
            pstmt.setString(1, article.getTitle());
            pstmt.setString(2, article.getContent());
            pstmt.setString(3, article.getHtmlContent() != null ? article.getHtmlContent() : "");
            pstmt.setTimestamp(4, new java.sql.Timestamp(article.getUpdateTime().getTime()));
            pstmt.setInt(5, article.getId());
            
            int rows = pstmt.executeUpdate();
            System.out.println("ArticleDao: SQL更新执行完成，影响行数: " + rows);
            return rows;
        } catch (SQLException e) {
            System.out.println("ArticleDao: SQL异常: " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
    }
    
    /**
     * 删除文章
     * @param id 文章ID
     * @return 影响行数（1=成功，0=失败）
     * @throws SQLException 数据库操作异常
     */
    public int delete(Integer id) throws SQLException {
        if (id == null || id <= 0) {
            return 0;
        }
        
        String sql = "DELETE FROM articles WHERE id = ?";
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            return pstmt.executeUpdate();
        }
    }
    
    /**
     * 增加文章阅读量（+1）
     * @param id 文章ID
     * @return 影响行数（1=成功，0=失败）
     * @throws SQLException 数据库操作异常
     */
    public int incrementViewCount(Integer id) throws SQLException {
        if (id == null || id <= 0) {
            return 0;
        }
        
        String sql = "UPDATE articles SET view_count = view_count + 1 WHERE id = ?";
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            return pstmt.executeUpdate();
        }
    }
}